<html>
<head>
  <title>Evernote Export</title>
  <basefont face="微软雅黑" size="2" />
  <meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
  <meta name="exporter-version" content="YXBJ Windows/600753 (zh-CN, DDL); Windows/10.0.0 (Win64);"/>
  <style>
    body, td {
      font-family: 微软雅黑;
      font-size: 10pt;
    }
  </style>
</head>
<body>
<a name="408"/>

<div>
<span><div><div><div style="text-align: center;"><span style="font-size: 18pt;">mysql查询练习</span></div><div style="text-align: center;"><font style="font-size: 18pt;"><br/></font></div><div style="text-align: left;">新建一个查询用的数据库:selectTest</div><div style="text-align: left;">CREATE DATABASE selectTest;</div><div style="text-align: left;">选择该数据库:</div><div style="text-align: left;">USE selectTest;</div><div style="text-align: left;"><br/></div><div style="text-align: left;">学生表:</div><div style="text-align: left;">student</div><div style="text-align: left;">学号</div><div style="text-align: left;">姓名</div><div style="text-align: left;">性别</div><div style="text-align: left;">出生日期</div><div style="text-align: left;">所在班级</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>CREATE TABLE student(</div><div>    s_no VARCHAR(20) PRIMARY KEY COMMENT'学生学号',</div><div>    s_name VARCHAR(20) NOT NULL COMMENT'学生姓名 不能为空',</div><div>    s_sex VARCHAR(10) NOT NULL COMMENT'学生性别',</div><div>    s_birthday DATETIME COMMENT'学生生日',</div><div>    s_class VARCHAR(20) COMMENT'学生所在的班级'</div><div>);</div></div><div style="text-align: left;"><br/></div><div style="text-align: left;"><br/></div><div style="text-align: left;">课程表:</div><div style="text-align: left;">course</div><div style="text-align: left;">课程号</div><div style="text-align: left;">课程课程名称</div><div style="text-align: left;">教师编号</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>CREATE TABLE course(</div><div>    c_no VARCHAR(20) PRIMARY KEY COMMENT'课程号',</div><div>    c_name VARCHAR(20) NOT NULL COMMENT'课程名称',</div><div>    t_no VARCHAR(20) NOT NULL COMMENT'教师编号 外键关联teacher表',</div><div>    FOREIGN KEY(t_no) references teacher(t_no)</div><div>);</div><div><br/></div><div>报错:Failed to open the referenced table 'teacher'</div><div>我们先创建teacher表</div></div><div style="text-align: left;"><br/></div><div style="text-align: left;"><br/></div><div style="text-align: left;">教师表</div><div style="text-align: left;">teacher</div><div style="text-align: left;">教师编号</div><div style="text-align: left;">教师名字</div><div style="text-align: left;">教师性别</div><div style="text-align: left;">出生日期</div><div style="text-align: left;">职称</div><div style="text-align: left;">所在部门</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>CREATE TABLE teacher(</div><div>    t_no VARCHAR(20) PRIMARY KEY COMMENT'教师编号',</div><div>    t_name VARCHAR(20) NOT NULL COMMENT'教师姓名',</div><div>    t_sex VARCHAR(20) NOT NULL COMMENT'教师性别',</div><div>    t_birthday DATETIME COMMENT'教师生日',</div><div>    t_rof VARCHAR(20) NOT NULL COMMENT'教师职称',</div><div>    t_depart VARCHAR(20) NOT NULL COMMENT'教师所在的部门'</div><div>);</div></div><div style="text-align: left;"><br/></div><div style="text-align: left;"><br/></div><div style="text-align: left;">成绩表</div><div style="text-align: left;">srore</div><div style="text-align: left;">学号</div><div style="text-align: left;">课程号</div><div style="text-align: left;">成绩</div><div style="text-align: left;"><span style="color: rgb(77, 206, 29);">注意:视频中原先只有一个主键s_no ,后来修改了</span></div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div><font color="#41AD1C">CREATE TABLE score (</font></div><div><font color="#41AD1C">    s_no VARCHAR(20) NOT NULL COMMENT'成绩表的编号 依赖学生学号',</font></div><div><font color="#41AD1C"><span style="font-size: 9pt; font-family: Monaco;">        c_no VARCHAR(20)  NOT NULL</span> COMMENT<span style="font-size: 9pt; font-family: Monaco;">'课程号 依赖于课程表中的c_id',</span></font></div><div><font color="#41AD1C">    sc_degree decimal,</font></div><div><font color="#41AD1C">    foreign key(s_no) references student(s_no),</font></div><div><font color="#41AD1C">    foreign key(c_no) references course(c_no),</font></div><div><font color="#41AD1C">    PRIMARY KEY(s_no,c_no)</font></div><div><font color="#41AD1C">);</font></div></div><div style="text-align: left;"><br/></div><div style="text-align: left;"><br/></div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">查看创建的表以及架构</span></div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">SHOW TABLES;</span></div><div>+----------------------+</div><div>| Tables_in_selecttest |</div><div>+----------------------+</div><div>| course               |</div><div>| score                |</div><div>| student              |</div><div>| teacher              |</div><div>+----------------------+</div><div><br/></div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">查看student表结构    DESCRIBE student;</span></div><div>+------------+-------------+------+-----+---------+-------+</div><div>| Field      | Type        | Null | Key | Default | Extra |</div><div>+------------+-------------+------+-----+---------+-------+</div><div>| s_no       | varchar(20) | NO   | PRI | NULL    |       |</div><div>| s_name     | varchar(20) | NO   |     | NULL    |       |</div><div>| s_sex      | varchar(10) | NO   |     | NULL    |       |</div><div>| s_birthday | datetime    | YES  |     | NULL    |       |</div><div>| s_class    | varchar(20) | YES  |     | NULL    |       |</div><div>+------------+-------------+------+-----+---------+-------+</div><div><br/></div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">查看teacher表结构    DESCRIBE teacher;</span></div><div>+------------+-------------+------+-----+---------+-------+</div><div>| Field      | Type        | Null | Key | Default | Extra |</div><div>+------------+-------------+------+-----+---------+-------+</div><div>| t_no       | varchar(20) | NO   | PRI | NULL    |       |</div><div>| t_name     | varchar(20) | NO   |     | NULL    |       |</div><div>| t_sex      | varchar(20) | NO   |     | NULL    |       |</div><div>| t_birthday | datetime    | YES  |     | NULL    |       |</div><div>| t_rof      | varchar(20) | NO   |     | NULL    |       |</div><div>| t_depart   | varchar(20) | NO   |     | NULL    |       |</div><div>+------------+-------------+------+-----+---------+-------+</div><div><br/></div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">查看course表结构    DESCRIBE course;</span></div><div>+--------+-------------+------+-----+---------+-------+</div><div>| Field  | Type        | Null | Key | Default | Extra |</div><div>+--------+-------------+------+-----+---------+-------+</div><div>| c_no   | varchar(20) | NO   | PRI | NULL    |       |</div><div>| c_name | varchar(20) | NO   |     | NULL    |       |</div><div>| t_no   | varchar(20) | NO   | MUL | NULL    |       |</div><div>+--------+-------------+------+-----+---------+-------+</div><div><br/></div><div>查看score表结构    DESCRIBE score;</div><div>+-----------+---------------+------+-----+---------+-------+</div><div>| Field     | Type          | Null | Key | Default | Extra |</div><div>+-----------+---------------+------+-----+---------+-------+</div><div>| s_no      | varchar(20)   | NO   | PRI | NULL    |       |</div><div>| c_no      | varchar(20)   | NO   | MUL | NULL    |       |</div><div>| sc_degree | decimal(10,0) | YES  |     | NULL    |       |</div><div>+-----------+---------------+------+-----+---------+-------+</div></div><div style="text-align: left;"><br/></div><div style="text-align: left;">向表中添加数据</div><div style="text-align: left;"><br/></div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>--学生表数据</div><div>INSERT INTO student VALUES('101','曾华','男','1977-09-01','95033');</div><div>INSERT INTO student VALUES('102','匡明','男','1975-10-02','95031');</div><div>INSERT INTO student VALUES('103','王丽','女','1976-01-23','95033');</div><div>INSERT INTO student VALUES('104','李军','男','1976-02-20','95033');</div><div>INSERT INTO student VALUES('105','王芳','女','1975-02-10','95031');</div><div>INSERT INTO student VALUES('106','陆军','男','1974-06-03','95031');</div><div>INSERT INTO student VALUES('107','王尼玛','男','1976-02-20','95033');</div><div>INSERT INTO student VALUES('108','张全蛋','男','1975-02-10','95031');</div><div>INSERT INTO student VALUES('109','赵铁柱','男','1974-06-03','95031');</div><div><br/></div><div>--教师表数据</div><div>INSERT INTO teacher VALUES('804','李诚','男','1958-12-02','副教授','计算机系');</div><div>INSERT INTO teacher VALUES('856','张旭','男','1969-03-12','讲师','电子工程系');</div><div>INSERT INTO teacher VALUES('825','王萍','女','1972-05-05','助教','计算机系');</div><div>INSERT INTO teacher VALUES('831','刘冰','女','1977-08-14','助教','电子工程系');</div><div><br/></div><div>--添加课程表</div><div>INSERT INTO course VALUES('3-105','计算机导论','825');</div><div>INSERT INTO course VALUES('3-245','操作系统','804');</div><div>INSERT INTO course VALUES('6-166','数字电路','856');</div><div>INSERT INTO course VALUES('9-888','高等数学','831');</div><div><br/></div><div>--添加成绩表</div><div>INSERT INTO score VALUES('103','3-245','86');</div><div>INSERT INTO score VALUES('105','3-245','75');</div><div>INSERT INTO score VALUES('109','3-245','68');</div><div>INSERT INTO score VALUES('103','3-105','92');</div><div><br/></div><div>INSERT INTO score VALUES('105','3-105','88');</div><div>INSERT INTO score VALUES('109','3-105','76');</div><div>INSERT INTO score VALUES('103','6-166','85');</div><div><br/></div><div>INSERT INTO score VALUES('105','6-166','79');</div><div>INSERT INTO score VALUES('109','6-166','81');</div></div><div style="text-align: left;"><br/></div><div style="text-align: left;"><font style="font-size: 12pt;"><span style="font-size: 12pt; font-weight: bold;">几张表的数据展现</span></font></div><div style="text-align: left;"><span style="font-weight: bold;">student </span> SELECT * FROM student;</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>+------+--------+-------+---------------------+---------+</div><div>| s_no | s_name | s_sex | s_birthday          | s_class |</div><div>+------+--------+-------+---------------------+---------+</div><div>| 101  | 曾华   | 男    | 1977-09-01 00:00:00 | 95033   |</div><div>| 102  | 匡明   | 男    | 1975-10-02 00:00:00 | 95031   |</div><div>| 103  | 王丽   | 女    | 1976-01-23 00:00:00 | 95033   |</div><div>| 104  | 李军   | 男    | 1976-02-20 00:00:00 | 95033   |</div><div>| 105  | 王芳   | 女    | 1975-02-10 00:00:00 | 95031   |</div><div>| 106  | 陆军   | 男    | 1974-06-03 00:00:00 | 95031   |</div><div>| 107  | 王尼玛 | 男    | 1976-02-20 00:00:00 | 95033   |</div><div>| 108  | 张全蛋 | 男    | 1975-02-10 00:00:00 | 95031   |</div><div>| 109  | 赵铁柱 | 男    | 1974-06-03 00:00:00 | 95031   |</div><div>+------+--------+-------+---------------------+---------+</div></div><div style="text-align: left;"><br/></div><div style="text-align: left;"><span style="font-weight: bold;">teacher </span> SELECT * FROM teacher;</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>+------+--------+-------+---------------------+--------+------------+</div><div>| t_no | t_name | t_sex | t_birthday          | t_rof  | t_depart   |</div><div>+------+--------+-------+---------------------+--------+------------+</div><div>| 804  | 李诚   | 男    | 1958-12-02 00:00:00 | 副教授 | 计算机系   |</div><div>| 825  | 王萍   | 女    | 1972-05-05 00:00:00 | 助教   | 计算机机系 |</div><div>| 831  | 刘冰   | 女    | 1977-08-14 00:00:00 | 助教   | 电子工程系 |</div><div>| 856  | 张旭   | 男    | 1969-03-12 00:00:00 | 讲师   | 电子工程系 |</div><div>+------+--------+-------+---------------------+--------+------------+</div></div><div style="text-align: left;"><br/></div><div style="text-align: left;"><span style="font-weight: bold;">score </span> SELECT * FROM score;</div><div style="text-align: left;"><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>+------+-------+-----------+</div><div>| s_no | c_no  | sc_degree |</div><div>+------+-------+-----------+</div><div>| 103  | 3-105 |        92 |</div><div>| 103  | 3-245 |        86 |</div><div>| 103  | 6-166 |        85 |</div><div>| 105  | 3-105 |        88 |</div><div>| 105  | 3-245 |        75 |</div><div>| 105  | 6-166 |        79 |</div><div>| 109  | 3-105 |        76 |</div><div>| 109  | 3-245 |        68 |</div><div>| 109  | 6-166 |        81 |</div><div>+------+-------+-----------+</div></div><div><br/></div></div><div style="text-align: left;"><span style="font-size: 10pt; font-weight: bold;">course</span><span style="font-size: 10pt; font-weight: bold;">   </span> <span style="font-size: 10pt;">SELECT * FROM course;</span></div><div style="text-align: left;"><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>+-------+------------+------+</div><div>| c_no  | c_name     | t_no |</div><div>+-------+------------+------+</div><div>| 3-105 | 计算机导论 | 825  |</div><div>| 3-245 | 操作系统   | 804  |</div><div>| 6-166 | 数字电路   | 856  |</div><div>| 9-888 | 高等数学   | 831  |</div><div>+-------+------------+------+</div></div><div><br/></div></div><hr/><div style="text-align: left;"><span style="font-size: 18pt;">查询联系</span></div><div style="text-align: left;">-- 1.查询student表中所有的记录</div><div style="text-align: left;"><br/></div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>SELECT * FROM student;</div><div>+------+--------+-------+---------------------+---------+</div><div>| s_no | s_name | s_sex | s_birthday          | s_class |</div><div>+------+--------+-------+---------------------+---------+</div><div>| 1    | admin  | 男    | 1999-09-09 00:00:00 | 95033   |</div><div>| 101  | 曾华   | 男    | 1977-09-01 00:00:00 | 95033   |</div><div>| 102  | 匡明   | 男    | 1975-10-02 00:00:00 | 95031   |</div><div>| 103  | 王丽   | 女    | 1976-01-23 00:00:00 | 95033   |</div><div>| 104  | 李军   | 男    | 1976-02-20 00:00:00 | 95033   |</div><div>| 105  | 王芳   | 女    | 1975-02-10 00:00:00 | 95031   |</div><div>| 106  | 陆军   | 男    | 1974-06-03 00:00:00 | 95031   |</div><div>| 107  | 王尼玛 | 男    | 1976-02-20 00:00:00 | 95033   |</div><div>| 108  | 张全蛋 | 男    | 1975-02-10 00:00:00 | 95031   |</div><div>| 109  | 赵铁柱 | 男    | 1974-06-03 00:00:00 | 95031   |</div><div>+------+--------+-------+---------------------+---------+</div></div><div style="text-align: left;"><br/></div><div style="text-align: left;"><br/></div><div style="text-align: left;">-- 2.查询student表中所有记录的s_name,s_sex和s_class列</div><div style="text-align: left;"><br/></div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>SELECT s_no,s_name,s_class FROM  student;</div><div>+------+--------+---------+</div><div>| s_no | s_name | s_class |</div><div>+------+--------+---------+</div><div>| 1    | admin  | 95033   |</div><div>| 101  | 曾华   | 95033   |</div><div>| 102  | 匡明   | 95031   |</div><div>| 103  | 王丽   | 95033   |</div><div>| 104  | 李军   | 95033   |</div><div>| 105  | 王芳   | 95031   |</div><div>| 106  | 陆军   | 95031   |</div><div>| 107  | 王尼玛 | 95033   |</div><div>| 108  | 张全蛋 | 95031   |</div><div>| 109  | 赵铁柱 | 95031   |</div><div>+------+--------+---------+</div></div><div style="text-align: left;"><br/></div><div style="text-align: left;"><br/></div><div style="text-align: left;">-- 3.查询教师所有的单位但是不重复的t_depart列</div><div style="text-align: left;"><br/></div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>SELECT distinct (t_depart) FROM teacher; </div><div>+------------+</div><div>| t_depart   |</div><div>+------------+</div><div>| 计算机系   |</div><div>| 计算机机系 |</div><div>| 电子工程系 |</div><div>+------------+</div></div><div style="text-align: left;"><br/></div><div style="text-align: left;"><br/></div><div style="text-align: left;">-- 4.查询score表中成绩在60-80之间所有的记录(sc_degree)</div><div style="text-align: left;"><span style="color: rgb(88, 152, 255);">注意:BETWEEN... ADN...    是包含边界的</span></div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>SELECT * FROM score WHERE sc_degree BETWEEN 61 AND 79;</div><div>SELECT * FROM score WHERE sc_degree &lt; 80 AND sc_degree &gt; 60 ;<span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;"> </span></div><div>+------+-------+-----------+</div><div>| s_no | c_no  | sc_degree |</div><div>+------+-------+-----------+</div><div>| 105  | 3-245 |        75 |</div><div>| 105  | 6-166 |        79 |</div><div>| 109  | 3-105 |        76 |</div><div>| 109  | 3-245 |        68 |</div><div>+------+-------+-----------+</div></div><div style="text-align: left;"><br/></div><div style="text-align: left;"><br/></div><div style="text-align: left;">-- 5.查询score表中成绩为85, 86, 或者88的记录(sc_degree)</div><div style="text-align: left;"><br/></div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>SELECT * FROM score WHERE sc_degree IN(85, 86, 88);</div><div>+------+-------+-----------+</div><div>| s_no | c_no  | sc_degree |</div><div>+------+-------+-----------+</div><div>| 103  | 3-245 |        86 |</div><div>| 103  | 6-166 |        85 |</div><div>| 105  | 3-105 |        88 |</div><div>+------+-------+-----------+</div></div><div style="text-align: left;"><br/></div><div style="text-align: left;"><br/></div><div style="text-align: left;">-- 6.查询student表中'95031'班或者性别为'女'的同学记录</div><div style="text-align: left;"><br/></div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>SELECT * FROM student WHERE s_class = '95031' OR s_sex = '女';</div><div>+------+--------+-------+---------------------+---------+</div><div>| s_no | s_name | s_sex | s_birthday          | s_class |</div><div>+------+--------+-------+---------------------+---------+</div><div>| 102  | 匡明   | 男    | 1975-10-02 00:00:00 | 95031   |</div><div>| 103  | 王丽   | 女    | 1976-01-23 00:00:00 | 95033   |</div><div>| 105  | 王芳   | 女    | 1975-02-10 00:00:00 | 95031   |</div><div>| 106  | 陆军   | 男    | 1974-06-03 00:00:00 | 95031   |</div><div>| 108  | 张全蛋 | 男    | 1975-02-10 00:00:00 | 95031   |</div><div>| 109  | 赵铁柱 | 男    | 1974-06-03 00:00:00 | 95031   |</div><div>+------+--------+-------+---------------------+---------+</div></div><div style="text-align: left;"><br/></div><div style="text-align: left;"><br/></div><div style="text-align: left;">-- 7.以class降序查询student表中所有的记录</div><div style="text-align: left;"><br/></div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>SELECT * FROM student ORDER BY s_class desc;</div><div>+------+--------+-------+---------------------+---------+</div><div>| s_no | s_name | s_sex | s_birthday          | s_class |</div><div>+------+--------+-------+---------------------+---------+</div><div>| 1    | admin  | 男    | 1999-09-09 00:00:00 | 95033   |</div><div>| 101  | 曾华   | 男    | 1977-09-01 00:00:00 | 95033   |</div><div>| 103  | 王丽   | 女    | 1976-01-23 00:00:00 | 95033   |</div><div>| 104  | 李军   | 男    | 1976-02-20 00:00:00 | 95033   |</div><div>| 107  | 王尼玛 | 男    | 1976-02-20 00:00:00 | 95033   |</div><div>| 102  | 匡明   | 男    | 1975-10-02 00:00:00 | 95031   |</div><div>| 105  | 王芳   | 女    | 1975-02-10 00:00:00 | 95031   |</div><div>| 106  | 陆军   | 男    | 1974-06-03 00:00:00 | 95031   |</div><div>| 108  | 张全蛋 | 男    | 1975-02-10 00:00:00 | 95031   |</div><div>| 109  | 赵铁柱 | 男    | 1974-06-03 00:00:00 | 95031   |</div><div>+------+--------+-------+---------------------+---------+</div></div><div style="text-align: left;"><br/></div><div style="text-align: left;"><br/></div><div style="text-align: left;">-- 8.以c_no升序.sc_degree降序插叙score表中所有的数据</div><div style="text-align: left;"><span style="color: rgb(88, 152, 255);">先以c_no进行升序,若c_no相同,则以sc_degree降序</span></div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">SELECT * FROM score ORDER BY c_no ASC,sc_degree DESC;</span></div><div>+------+-------+-----------+</div><div>| s_no | c_no  | sc_degree |</div><div>+------+-------+-----------+</div><div>| 103  | 3-105 |        92 |</div><div>| 105  | 3-105 |        88 |</div><div>| 109  | 3-105 |        76 |</div><div>| 103  | 3-245 |        86 |</div><div>| 105  | 3-245 |        75 |</div><div>| 109  | 3-245 |        68 |</div><div>| 103  | 6-166 |        85 |</div><div>| 109  | 6-166 |        81 |</div><div>| 105  | 6-166 |        79 |</div><div>+------+-------+-----------+</div></div><div style="text-align: left;"><br/></div><div style="text-align: left;"><br/></div><div style="text-align: left;">-- 9.查询'95031'班的学生人数</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">SELECT COUNT(s_no) FROM student WHERE s_class = '95031';</span></div><div>+-------------+</div><div>| COUNT(s_no) |</div><div>+-------------+</div><div>|           5 |</div><div>+-------------+</div></div><div style="text-align: left;"><br/></div><div style="text-align: left;"><br/></div><div style="text-align: left;">-- 10.查询score表中的最高分数的学生号和课程号.(子查询或者排序)</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">--查询步骤</span></div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">1.先找到最高分</span></div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">最高分: SELECT MAX(sc_degree) FROM score;</span></div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">2.通过我们找到的最高分的分数来从score中找到我们需要的学生号和课程号</span></div><div>SELECT c_no, s_no FROM score WHERE sc_degree = (最高分)</div><div><br/></div><div>SELECT c_no, s_no FROM score WHERE sc_degree = (SELECT MAX(sc_degree) FROM score);</div><div>+-------+------+</div><div>| c_no  | s_no |</div><div>+-------+------+</div><div>| 3-105 | 103  |</div><div>+-------+------+</div><div><br/></div><div><b><font color="#5898FF">排序 最高分有多个的情况下可能有数据问题</font></b></div><div><font color="#5898FF">limit x,y  (x:表示从X条数据开始  y:需要查出多少条)</font></div><div><font color="#5898FF">SELECT c_no, s_no FROM score ORDER BY sc_degree DESC LIMIT 0,1;这个OK</font></div><div><font color="#5898FF">但是我们再插入一条数据:</font></div><div><font color="#5898FF">INSERT INTO score VALUES('101','9-888','92');</font></div><div><font color="#5898FF">再用排序法去查得到:</font></div><div><font color="#5898FF">+-------+------+</font></div><div><font color="#5898FF">| c_no  | s_no |</font></div><div><font color="#5898FF">+-------+------+</font></div><div><font color="#5898FF">| 9-888 | 101  |</font></div><div><font color="#5898FF">+-------+------+</font></div><div><span style="font-size: 9pt; font-family: Monaco;"><font color="#5898FF">有两条数据但是只显示一条,有问题</font></span></div><div><br/></div><div><font color="#5898FF">若我们用子查询的方法来查询的话会得到:</font></div><div><font color="#5898FF">+-------+------+</font></div><div><font color="#5898FF">| c_no  | s_no |</font></div><div><font color="#5898FF">+-------+------+</font></div><div><font color="#5898FF">| 9-888 | 101  |</font></div><div><font color="#5898FF">| 3-105 | 103  |</font></div><div><font color="#5898FF">+-------+------+</font></div><div><font color="#5898FF">完全没有问题.</font></div><div><font color="#5898FF">最后为了和视频数据一致删除刚刚插入的数据:</font></div><div><font color="#5898FF">DELETE FROM score WHERE c_no = '9-888' AND s_no = '101';</font></div></div><div><br/></div></div><div><br/></div><div>-- 11.查询每门课的平均成绩</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div><font color="#000000">SELECT c_no,AVG(sc_degree) FROM SCORE GROUP BY c_no;</font></div><div>+-------+----------------+</div><div>| c_no  | AVG(sc_degree) |</div><div>+-------+----------------+</div><div>| 3-105 |        87.6667 |</div><div>| 3-245 |        76.3333 |</div><div>| 6-166 |        81.6667 |</div><div>+-------+----------------+</div></div><div><br/></div><div><br/></div><div><br/></div><div>-- 12,查询score表中至少有2名学生选修的,并且以3开头的课程的平均分</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>SELECT AVG(sc_degree),c_no  from score GROUP BY c_no HAVING COUNT(c_no)  &gt;= 2 AND c_no like '3%'  ;</div><div>+----------------+-------+</div><div>| AVG(sc_degree) | c_no  |</div><div>+----------------+-------+</div><div>|        85.3333 | 3-105 |</div><div>|        76.3333 | 3-245 |</div><div>+----------------+-------+</div><div><br/></div></div><div><br/></div><div><br/></div><div><br/></div><div>-- 13.查询分数大于70但是小于90的s_no列:</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>SELECT s_no,sc_degree  FROM score WHERE sc_degree BETWEEN 71 AND 89;</div><div>+------+-----------+</div><div>| s_no | sc_degree |</div><div>+------+-----------+</div><div>| 103  |        86 |</div><div>| 103  |        85 |</div><div>| 105  |        88 |</div><div>| 105  |        75 |</div><div>| 105  |        79 |</div><div>| 109  |        76 |</div><div>| 109  |        81 |</div><div>+------+-----------+</div><div><br/></div><div><span style="font-size: 9pt; font-family: Monaco;"><font color="#5898FF">进阶:显示s_name,c_name</font></span></div><div><font color="#5898FF">select s_name,sc_degree,c_name FROM score,student,course WHERE score.s_no = student.s_no AND score.c_no = course.c_no AND sc_degree BETWEEN 71 AND 89;</font></div><div><font color="#5898FF">+--------+-----------+------------+</font></div><div><font color="#5898FF">| s_name | sc_degree | c_name     |</font></div><div><font color="#5898FF">+--------+-----------+------------+</font></div><div><font color="#5898FF">| 王丽   |        86 | 操作系统   |</font></div><div><font color="#5898FF">| 王丽   |        85 | 数字电路   |</font></div><div><font color="#5898FF">| 王芳   |        88 | 计算机导论 |</font></div><div><font color="#5898FF">| 王芳   |        75 | 操作系统   |</font></div><div><font color="#5898FF">| 王芳   |        79 | 数字电路   |</font></div><div><font color="#5898FF">| 赵铁柱 |        76 | 计算机导论 |</font></div><div><font color="#5898FF">| 赵铁柱 |        81 | 数字电路   |</font></div><div><font color="#5898FF">+--------+-----------+------------+</font></div></div><div><br/></div><div><br/></div><div>-- 14.查询所有的学生 s_name , c_no, sc_degree列</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>SELECT s_name, c_no, sc_degree FROM student,score WHERE student.s_no = score.s_no;</div><div>+--------+-------+-----------+</div><div>| s_name | c_no  | sc_degree |</div><div>+--------+-------+-----------+</div><div>| 王丽   | 3-105 |        92 |</div><div>| 王丽   | 3-245 |        86 |</div><div>| 王丽   | 6-166 |        85 |</div><div>| 王芳   | 3-105 |        88 |</div><div>| 王芳   | 3-245 |        75 |</div><div>| 王芳   | 6-166 |        79 |</div><div>| 赵铁柱 | 3-105 |        76 |</div><div>| 赵铁柱 | 3-245 |        68 |</div><div>| 赵铁柱 | 6-166 |        81 |</div><div>+--------+-------+-----------+</div></div><div><br/></div><div><br/></div><div>--  15.查询所有学生的s_no, c_name, sc_degree列</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>SELECT student.s_no, c_name, sc_degree FROM student, course,score WHERE student.s_no = score.s_no AND score.c_no = course.c_no ;</div><div>+------+------------+-----------+</div><div>| s_no | c_name     | sc_degree |</div><div>+------+------------+-----------+</div><div>| 103  | 计算机导论 |        92 |</div><div>| 103  | 操作系统   |        86 |</div><div>| 103  | 数字电路   |        85 |</div><div>| 105  | 计算机导论 |        88 |</div><div>| 105  | 操作系统   |        75 |</div><div>| 105  | 数字电路   |        79 |</div><div>| 109  | 计算机导论 |        76 |</div><div>| 109  | 操作系统   |        68 |</div><div>| 109  | 数字电路   |        81 |</div><div>+------+------------+-----------+</div></div><div><br/></div><div><br/></div><div>-- 16.查询所有的学生 s_name , c_name, sc_degree列</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>将上面的c_no 的值换位c_name</div><div>SELECT s_name, c_name, sc_degree FROM student, course, score WHERE student.s_no = score.s_no AND score.c_no = course.c_no;</div><div>+--------+------------+-----------+</div><div>| s_name | c_name     | sc_degree |</div><div>+--------+------------+-----------+</div><div>| 王丽   | 计算机导论 |        92 |</div><div>| 王丽   | 操作系统   |        86 |</div><div>| 王丽   | 数字电路   |        85 |</div><div>| 王芳   | 计算机导论 |        88 |</div><div>| 王芳   | 操作系统   |        75 |</div><div>| 王芳   | 数字电路   |        79 |</div><div>| 赵铁柱 | 计算机导论 |        76 |</div><div>| 赵铁柱 | 操作系统   |        68 |</div><div>| 赵铁柱 | 数字电路   |        81 |</div><div>+--------+------------+-----------+</div></div><div><br/></div><div><br/></div><div>-- 17.查询班级是'95031'班学生每门课的平均分</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>select c_no,AVG(sc_degree) from score WHERE s_no IN (select s_no from student where s_class = '95031') GROUP BY c_no;</div><div><br/></div><div><font color="#5898FF">select c_no,AVG(sc_degree) from score AS sc LEFT JOIN student AS s ON sc.s_no = s.s_no WHERE s.s_class = '95031' GROUP BY sc.c_no;</font></div><div><font color="#5898FF"><br/></font></div><div><font color="#5898FF">SELECT sc.c_no,AVG(sc.sc_degree) FROM student AS s, score AS SC  WHERE s.s_class = '95031' AND s.s_no = sc.s_no GROUP BY sc.c_no ;</font></div><div>+-------+-------------------+</div><div>| c_no  | AVG(sc.sc_degree) |</div><div>+-------+-------------------+</div><div>| 3-105 |           82.0000 |</div><div>| 3-245 |           71.5000 |</div><div>| 6-166 |           80.0000 |</div><div>+-------+-------------------+</div><div><span style="font-size: 9pt; font-family: Monaco;"><font color="#5898FF">进阶,加入课程名称:</font></span></div><div><font color="#5898FF">SELECT sc.c_no, c.c_name, AVG(sc.sc_degree) FROM student AS s, score AS SC, course AS c  WHERE s.s_class = '95031' AND s.s_no = sc.s_no AND c.c_no = sc.c_no GROUP BY sc.c_no ;</font></div><div><font color="#5898FF">+-------+------------+-------------------+</font></div><div><font color="#5898FF">| c_no  | c_name     | AVG(sc.sc_degree) |</font></div><div><font color="#5898FF">+-------+------------+-------------------+</font></div><div><font color="#5898FF">| 3-105 | 计算机导论 |           82.0000 |</font></div><div><font color="#5898FF">| 3-245 | 操作系统   |           71.5000 |</font></div><div><font color="#5898FF">| 6-166 | 数字电路   |           80.0000 |</font></div><div><font color="#5898FF">+-------+------------+-------------------+</font></div><div><br/></div></div><div><span style="font-family: Monaco;"><br/></span></div><div><br/></div><div><br/></div><div>-- 18.查询选修&quot;3-105&quot;课程的成绩高于'109'号同学'3-105'成绩 的所有同学的记录</div><div><font style="color: rgb(88, 152, 255);">(在大家都在选修3-105的背景下 查询 所有 分数 比 学号为&quot;109&quot;还要高的学生信息)</font></div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>SELECT * FROM student AS s, score AS sc WHERE sc.c_no = '3-105' AND sc.sc_degree &gt; (SELECT sc_degree FROM score WHERE s_no = '109' AND c_no = '3-105') AND s.s_no = sc.s_no;</div><div><br/></div><div><br/></div><div>+------+--------+-------+---------------------+---------+------+-------+-----------+</div><div>| s_no | s_name | s_sex | s_birthday          | s_class | s_no | c_no  | sc_degree |</div><div>+------+--------+-------+---------------------+---------+------+-------+-----------+</div><div>| 103  | 王丽   | 女    | 1976-01-23 00:00:00 | 95033   | 103  | 3-105 |        92 |</div><div>| 105  | 王芳   | 女    | 1975-02-10 00:00:00 | 95031   | 105  | 3-105 |        88 |</div><div>+------+--------+-------+---------------------+---------+------+-------+-----------+</div><div><br/></div><div><br/></div></div><div><br/></div><div><br/></div><div>-- 19.查询成绩高于学号为'109',课程号为'3-105'的成绩的所有记录</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">SELECT * FROM score WHERE sc_degree &gt;(SELECT sc_degree FROM score WHERE s_no = '109' AND c_no = '3-105');</span></div><div>+------+-------+-----------+</div><div>| s_no | c_no  | sc_degree |</div><div>+------+-------+-----------+</div><div>| 103  | 3-105 |        92 |</div><div>| 103  | 3-245 |        86 |</div><div>| 103  | 6-166 |        85 |</div><div>| 105  | 3-105 |        88 |</div><div>| 105  | 6-166 |        79 |</div><div>| 109  | 6-166 |        81 |</div><div>+------+-------+-----------+</div><div><br/></div><div><span style="font-size: 9pt; font-family: Monaco;"><font color="#5898FF">不过视频中仅仅查出来了score记录,但是并没有学生的信息,按照上面的来修改:</font></span></div><div><font color="#5898FF">SELECT * FROM student AS s, score AS sc WHERE sc.sc_degree &gt; (SELECT sc_degree FROM score WHERE s_no = '109' AND c_no = '3-105') AND s.s_no = sc.s_no;</font></div><div><font color="#5898FF"><br/></font></div><div><font color="#5898FF">还可以再进一步:</font></div><div><font color="#5898FF">SELECT s.s_no AS'学生学号', s.s_name AS'学生姓名', s_sex AS'性别', s_class AS'班级', c.c_no AS'课程编号', c.c_name AS'课程名称' ,sc.sc_degree AS'分数' FROM student AS s, score AS sc ,course AS c WHERE sc.sc_degree &gt; (SELECT sc_degree FROM score WHERE s_no = '109' AND c_no = '3-105') AND s.s_no = sc.s_no AND sc.c_no = c.c_no;</font></div><div><font color="#5898FF">+----------+----------+------+-------+----------+------------+------+</font></div><div><font color="#5898FF">| 学生学号 | 学生姓名 | 性别 | 班级  | 课程编号 | 课程名称   | 分数 |</font></div><div><font color="#5898FF">+----------+----------+------+-------+----------+------------+------+</font></div><div><font color="#5898FF">| 103      | 王丽     | 女   | 95033 | 3-105    | 计算机导论 |   92 |</font></div><div><font color="#5898FF">| 103      | 王丽     | 女   | 95033 | 3-245    | 操作系统   |   86 |</font></div><div><font color="#5898FF">| 103      | 王丽     | 女   | 95033 | 6-166    | 数字电路   |   85 |</font></div><div><font color="#5898FF">| 105      | 王芳     | 女   | 95031 | 3-105    | 计算机导论 |   88 |</font></div><div><font color="#5898FF">| 105      | 王芳     | 女   | 95031 | 6-166    | 数字电路   |   79 |</font></div><div><font color="#5898FF">| 109      | 赵铁柱   | 男   | 95031 | 6-166    | 数字电路   |   81 |</font></div><div><font color="#5898FF">+----------+----------+------+-------+----------+------------+------+</font></div><div><br/></div></div><div><br/></div><div><br/></div><div>--  20.查询所有学号为108.101的同学同年出生的所有学生的s_no,s_name和s_birthday</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>SELECT * FROM student WHERE YEAR(s_birthday) IN (SELECT YEAR(s_birthday) FROM student WHERE s_no IN('108','101'));</div><div>+------+--------+-------+---------------------+---------+</div><div>| s_no | s_name | s_sex | s_birthday          | s_class |</div><div>+------+--------+-------+---------------------+---------+</div><div>| 101  | 曾华   | 男    | 1977-09-01 00:00:00 | 95033   |</div><div>| 102  | 匡明   | 男    | 1975-10-02 00:00:00 | 95031   |</div><div>| 105  | 王芳   | 女    | 1975-02-10 00:00:00 | 95031   |</div><div>| 108  | 张全蛋 | 男    | 1975-02-10 00:00:00 | 95031   |</div><div>+------+--------+-------+---------------------+---------+</div><div><br/></div></div><div><br/></div><div><br/></div><div>-- 21.查询 张旭 教师任课的学生的成绩</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>select * from student where s_no IN (SELECT s_no FROM score WHERE c_no = (SELECT c_no FROM course WHERE t_no = (SELECT t_no FROM teacher WHERE t_name='张旭')));</div><div><br/></div><div>+------+--------+-------+---------------------+---------+</div><div>| s_no | s_name | s_sex | s_birthday          | s_class |</div><div>+------+--------+-------+---------------------+---------+</div><div>| 103  | 王丽   | 女    | 1976-01-23 00:00:00 | 95033   |</div><div>| 105  | 王芳   | 女    | 1975-02-10 00:00:00 | 95031   |</div><div>| 109  | 赵铁柱 | 男    | 1974-06-03 00:00:00 | 95031   |</div><div>+------+--------+-------+---------------------+---------+</div></div><div><br/></div><div><br/></div><div>-- 22.查询选修课程的同学人数多余 5 人的教师姓名</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>SELECT * FROM teacher WHERE t_no IN(SELECT t_no FROM course WHERE c_no IN (SELECT c_no FROM score GROUP BY c_no HAVING COUNT(s_no) &gt; 5));</div><div><br/></div><div>Empty set (0.00 sec)      没有符合条件的</div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">为了效果,添加数据:</span></div><div>INSERT INTO score VALUES('101','3-105','90');</div><div>INSERT INTO score VALUES('102','3-105','91');</div><div>INSERT INTO score VALUES('104','3-105','89');</div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;"><br/></span></div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">再次查询可得:</span></div><div>+------+--------+-------+---------------------+-------+------------+</div><div>| t_no | t_name | t_sex | t_birthday          | t_rof | t_depart   |</div><div>+------+--------+-------+---------------------+-------+------------+</div><div>| 825  | 王萍   | 女    | 1972-05-05 00:00:00 | 助教  | 计算机机系 |</div><div>+------+--------+-------+---------------------+-------+------------+</div><div><font color="#5898FF">注意:视频中用 '=' 是不严谨的,实际中你根本不知道有多少条件是符合的,要用IN</font></div></div><div><br/></div><div><br/></div><div>-- 23.查询95033班和95031班全体学生的记录</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>SELECT * FROM student WHERE s_class IN('95031','95033') ORDER BY s_class ;</div><div>+------+--------+-------+---------------------+---------+</div><div>| s_no | s_name | s_sex | s_birthday          | s_class |</div><div>+------+--------+-------+---------------------+---------+</div><div>| 102  | 匡明   | 男    | 1975-10-02 00:00:00 | 95031   |</div><div>| 105  | 王芳   | 女    | 1975-02-10 00:00:00 | 95031   |</div><div>| 106  | 陆军   | 男    | 1974-06-03 00:00:00 | 95031   |</div><div>| 108  | 张全蛋 | 男    | 1975-02-10 00:00:00 | 95031   |</div><div>| 109  | 赵铁柱 | 男    | 1974-06-03 00:00:00 | 95031   |</div><div>| 101  | 曾华   | 男    | 1977-09-01 00:00:00 | 95033   |</div><div>| 103  | 王丽   | 女    | 1976-01-23 00:00:00 | 95033   |</div><div>| 104  | 李军   | 男    | 1976-02-20 00:00:00 | 95033   |</div><div>| 107  | 王尼玛 | 男    | 1976-02-20 00:00:00 | 95033   |</div><div>+------+--------+-------+---------------------+---------+</div><div><br/></div><div>由于视频中就只有这两个班,所以要插入数据:</div><div>INSERT INTO student VALUES('110','张飞','男','1974-06-03','95038');</div><div>再次查询,还是上面那些</div><div><br/></div></div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div><font color="#5898FF">我在写的时候,写成了:查询95033班和95031班全体学生每门课的成绩以及负责该课程的老师,最后以class来排序</font></div><div><span style="font-size: 9pt; font-family: Monaco;"><font color="#5898FF">写都写了,那就放出来:</font></span></div><div><font color="#5898FF">SELECT s.s_no, s.s_name,s.s_birthday,s.s_class, c.c_no, c.c_name, sc.sc_degree , t.t_name FROM student AS s, course AS c, score AS sc,teacher AS t WHERE  s.s_class IN('95031','95033') AND s.s_no = sc.s_no AND sc.c_no = c.c_no AND c.t_no = t.t_no;</font></div><div><font color="#5898FF">+------+--------+---------------------+---------+-------+------------+-----------+--------+</font></div><div><font color="#5898FF">| s_no | s_name | s_birthday          | s_class | c_no  | c_name     | sc_degree | t_name |</font></div><div><font color="#5898FF">+------+--------+---------------------+---------+-------+------------+-----------+--------+</font></div><div><font color="#5898FF">| 102  | 匡明   | 1975-10-02 00:00:00 | 95031   | 3-105 | 计算机导论 |        91 | 王萍   |</font></div><div><font color="#5898FF">| 105  | 王芳   | 1975-02-10 00:00:00 | 95031   | 3-105 | 计算机导论 |        88 | 王萍   |</font></div><div><font color="#5898FF">| 105  | 王芳   | 1975-02-10 00:00:00 | 95031   | 3-245 | 操作系统   |        75 | 李诚   |</font></div><div><font color="#5898FF">| 105  | 王芳   | 1975-02-10 00:00:00 | 95031   | 6-166 | 数字电路   |        79 | 张旭   |</font></div><div><font color="#5898FF">| 109  | 赵铁柱 | 1974-06-03 00:00:00 | 95031   | 3-105 | 计算机导论 |        76 | 王萍   |</font></div><div><font color="#5898FF">| 109  | 赵铁柱 | 1974-06-03 00:00:00 | 95031   | 3-245 | 操作系统   |        68 | 李诚   |</font></div><div><font color="#5898FF">| 109  | 赵铁柱 | 1974-06-03 00:00:00 | 95031   | 6-166 | 数字电路   |        81 | 张旭   |</font></div><div><font color="#5898FF">| 101  | 曾华   | 1977-09-01 00:00:00 | 95033   | 3-105 | 计算机导论 |        90 | 王萍   |</font></div><div><font color="#5898FF">| 103  | 王丽   | 1976-01-23 00:00:00 | 95033   | 3-105 | 计算机导论 |        92 | 王萍   |</font></div><div><font color="#5898FF">| 103  | 王丽   | 1976-01-23 00:00:00 | 95033   | 3-245 | 操作系统   |        86 | 李诚   |</font></div><div><font color="#5898FF">| 103  | 王丽   | 1976-01-23 00:00:00 | 95033   | 6-166 | 数字电路   |        85 | 张旭   |</font></div><div><font color="#5898FF">| 104  | 李军   | 1976-02-20 00:00:00 | 95033   | 3-105 | 计算机导论 |        89 | 王萍   |</font></div><div><font color="#5898FF">+------+--------+---------------------+---------+-------+------------+-----------+--------+</font></div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;"><br/></span></div></div><div><br/></div><div><br/></div><div>-- 24.查询存在85分以上成绩的课程c_no</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>SELECT * FROM score where sc_degree &gt; 85;</div><div>+------+-------+-----------+</div><div>| s_no | c_no  | sc_degree |</div><div>+------+-------+-----------+</div><div>| 101  | 3-105 |        90 |</div><div>| 102  | 3-105 |        91 |</div><div>| 103  | 3-105 |        92 |</div><div>| 103  | 3-245 |        86 |</div><div>| 104  | 3-105 |        89 |</div><div>| 105  | 3-105 |        88 |</div><div>+------+-------+-----------+</div><div><br/></div><div><span style="font-size: 9pt; font-family: Monaco;"><font color="#5898FF">升级版:知道c_no还不行,我们还需要看到c_name 以及对应的老师</font></span></div><div><font color="#5898FF">SELECT sc.c_no,c.c_name, t.t_name FROM score AS sc, course AS c, teacher AS t WHERE sc.c_no IN(SELECT c_no FROM score where sc_degree &gt; 85) AND sc.c_no = c.c_no AND c.t_no = t.t_no GROUP BY c.c_name;</font></div><div><font color="#5898FF">+-------+------------+--------+</font></div><div><font color="#5898FF">| c_no  | c_name     | t_name |</font></div><div><font color="#5898FF">+-------+------------+--------+</font></div><div><font color="#5898FF">| 3-105 | 计算机导论 | 王萍   |</font></div><div><font color="#5898FF">| 3-245 | 操作系统   | 李诚   |</font></div><div><font color="#5898FF">+-------+------------+--------+</font></div><div><font color="#5898FF">这两位老师教出来的学生都有85分以上的</font></div></div><div><br/></div><div><br/></div><div>-- 25.查出所有'计算机系' 教师所教课程的成绩表</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>SELECT * FROM score WHERE c_no IN (SELECT c_no FROM course WHERE t_no IN (SELECT t_no FROM teacher WHERE t_depart = '计算机系'));</div><div>+------+-------+-----------+</div><div>| s_no | c_no  | sc_degree |</div><div>+------+-------+-----------+</div><div>| 103  | 3-245 |        86 |</div><div>| 105  | 3-245 |        75 |</div><div>| 109  | 3-245 |        68 |</div><div>| 101  | 3-105 |        90 |</div><div>| 102  | 3-105 |        91 |</div><div>| 103  | 3-105 |        92 |</div><div>| 104  | 3-105 |        89 |</div><div>| 105  | 3-105 |        88 |</div><div>| 109  | 3-105 |        76 |</div><div>+------+-------+-----------+</div><div><br/></div><div><span style="font-size: 9pt; font-family: Monaco;"><font color="#5898FF">进阶:</font></span></div><div><font color="#5898FF">SELECT  t.t_name,t.t_depart,c.c_name,s.s_name,s_class,sc.sc_degree FROM course AS c, score AS sc, teacher AS t,student AS s WHERE c.t_no IN (select t_no FROM teacher WHERE t_depart = '计算机系') AND c.t_no = t.t_no AND c.c_no = sc.c_no AND sc.s_no = s.s_no ;</font></div><div><font color="#5898FF"> 教师名称  部门        课程名称      学生名称  班级        分数</font></div><div><font color="#5898FF">+--------+----------+------------+--------+---------+-----------+</font></div><div><font color="#5898FF">| t_name | t_depart | c_name     | s_name | s_class | sc_degree |</font></div><div><font color="#5898FF">+--------+----------+------------+--------+---------+-----------+</font></div><div><font color="#5898FF">| 李诚   | 计算机系 | 操作系统   | 王丽   | 95033   |        86 |</font></div><div><font color="#5898FF">| 李诚   | 计算机系 | 操作系统   | 王芳   | 95031   |        75 |</font></div><div><font color="#5898FF">| 李诚   | 计算机系 | 操作系统   | 赵铁柱 | 95031   |        68 |</font></div><div><font color="#5898FF">| 王萍   | 计算机系 | 计算机导论 | 曾华   | 95033   |        90 |</font></div><div><font color="#5898FF">| 王萍   | 计算机系 | 计算机导论 | 匡明   | 95031   |        91 |</font></div><div><font color="#5898FF">| 王萍   | 计算机系 | 计算机导论 | 王丽   | 95033   |        92 |</font></div><div><font color="#5898FF">| 王萍   | 计算机系 | 计算机导论 | 李军   | 95033   |        89 |</font></div><div><font color="#5898FF">| 王萍   | 计算机系 | 计算机导论 | 王芳   | 95031   |        88 |</font></div><div><font color="#5898FF">| 王萍   | 计算机系 | 计算机导论 | 赵铁柱 | 95031   |        76 |</font></div><div><font color="#5898FF">+--------+----------+------------+--------+---------+-----------+</font></div><div><font color="#5898FF"><br/></font></div></div><div><br/></div><div><br/></div><div>-- 26.查询'计算机系'与'电子工程系' 不同职称的教师的name和rof</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>SELECT * FROM teacher WHERE t_depart = '计算机系' AND t_rof NOT IN (SELECT t_rof FROM teacher WHERE t_depart = '电子工程系')</div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">UNION</span></div><div>SELECT * FROM teacher WHERE t_depart = '电子工程系' AND t_rof NOT IN (SELECT t_rof FROM teacher WHERE t_depart = '计算机系');</div><div><br/></div><div>+------+--------+-------+---------------------+--------+------------+</div><div>| t_no | t_name | t_sex | t_birthday          | t_rof  | t_depart   |</div><div>+------+--------+-------+---------------------+--------+------------+</div><div>| 804  | 李诚   | 男    | 1958-12-02 00:00:00 | 副教授 | 计算机系   |</div><div>| 856  | 张旭   | 男    | 1969-03-12 00:00:00 | 讲师   | 电子工程系 |</div><div>+------+--------+-------+---------------------+--------+------------+</div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;"><br/></span></div></div><div><br/></div><div><br/></div><div>-- 27, 查询选修编号为&quot;3-105&quot;课程且成绩至少高于选修编号为'3-245'同学的c_no,s_no和sc_degree,并且按照sc_degree从高到地次序排序</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>select * from score where c_no = '3-105' AND sc_degree &gt; ANY(SELECT sc_degree FROM score WHERE c_no = '3-245' ) ORDER BY sc_degree desc ;</div><div><br/></div><div>+------+-------+-----------+</div><div>| s_no | c_no  | sc_degree |</div><div>+------+-------+-----------+</div><div>| 103  | 3-105 |        92 |</div><div>| 102  | 3-105 |        91 |</div><div>| 101  | 3-105 |        90 |</div><div>| 104  | 3-105 |        89 |</div><div>| 105  | 3-105 |        88 |</div><div>| 109  | 3-105 |        76 |</div><div>+------+-------+-----------+</div></div><div><br/></div><div><br/></div><div>-- 28.查询选修编号为&quot;3-105&quot;且成绩高于选修编号为&quot;3-245&quot;课程的同学c_no.s_no和sc_degree</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>SELECT * FROM score WHERE sc_degree &gt; ALL (select sc_degree from score WHERE c_no = '3-245') AND c_no = '3-105';</div><div>+------+-------+-----------+</div><div>| s_no | c_no  | sc_degree |</div><div>+------+-------+-----------+</div><div>| 101  | 3-105 |        90 |</div><div>| 102  | 3-105 |        91 |</div><div>| 103  | 3-105 |        92 |</div><div>| 104  | 3-105 |        89 |</div><div>| 105  | 3-105 |        88 |</div><div>+------+-------+-----------+</div><div><br/></div><div><span style="font-size: 9pt; font-family: Monaco;"><font color="#5898FF">进阶:查出学生的信息,课程名称,分数(s_name c_name,sc_degree)</font></span></div><div><font color="#5898FF">SELECT s.s_name , c.c_name ,sc.sc_degree FROM score AS sc, student AS s,course AS c WHERE sc_degree &gt; ALL (select sc_degree from score WHERE c.c_no = '3-245') AND c.c_no = '3-105' AND sc.s_no = s.s_no AND sc.c_no = c.c_no ;</font></div><div><font color="#5898FF">+--------+------------+-----------+</font></div><div><font color="#5898FF">| s_name | c_name     | sc_degree |</font></div><div><font color="#5898FF">+--------+------------+-----------+</font></div><div><font color="#5898FF">| 曾华   | 计算机导论 |        90 |</font></div><div><font color="#5898FF">| 匡明   | 计算机导论 |        91 |</font></div><div><font color="#5898FF">| 王丽   | 计算机导论 |        92 |</font></div><div><font color="#5898FF">| 李军   | 计算机导论 |        89 |</font></div><div><font color="#5898FF">| 王芳   | 计算机导论 |        88 |</font></div><div><font color="#5898FF">| 赵铁柱 | 计算机导论 |        76 |</font></div><div><font color="#5898FF">+--------+------------+-----------+</font></div></div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">总结: ANY 和 ALL</span></div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">ANY:表示任何一个就行了,如;数组A中的值比数组B中任何一个都要大,那么只要A和B中最小的比较就行了.</span></div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">ALL:表示所有都要比较,如:数组A中的值比数组B中所有的数都要大,那么A要和B中最大的值比较才行.</span></div></div><div><br/></div><div><br/></div><div>-- 29. 查询所有教师和同学的 name ,sex, birthday</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>SELECT s_name AS name, s_sex AS sex, s_birthday AS birthday  FROM student</div><div>UNION</div><div>SELECT t_name AS name, t_sex AS sex, t_birthday AS birthday FROM teacher;</div><div>+--------+-----+---------------------+</div><div>| name   | sex | birthday            |</div><div>+--------+-----+---------------------+</div><div>| 曾华   | 男  | 1977-09-01 00:00:00 |</div><div>| 匡明   | 男  | 1975-10-02 00:00:00 |</div><div>| 王丽   | 女  | 1976-01-23 00:00:00 |</div><div>| 李军   | 男  | 1976-02-20 00:00:00 |</div><div>| 王芳   | 女  | 1975-02-10 00:00:00 |</div><div>| 陆军   | 男  | 1974-06-03 00:00:00 |</div><div>| 王尼玛 | 男  | 1976-02-20 00:00:00 |</div><div>| 张全蛋 | 男  | 1975-02-10 00:00:00 |</div><div>| 赵铁柱 | 男  | 1974-06-03 00:00:00 |</div><div>| 张飞   | 男  | 1974-06-03 00:00:00 |</div><div>| 李诚   | 男  | 1958-12-02 00:00:00 |</div><div>| 王萍   | 女  | 1972-05-05 00:00:00 |</div><div>| 刘冰   | 女  | 1977-08-14 00:00:00 |</div><div>| 张旭   | 男  | 1969-03-12 00:00:00 |</div><div>+--------+-----+---------------------+</div></div><br/><div><br/></div><div>-- 30.查询所有'女'教师和'女'学生的name,sex,birthday</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>SELECT s_name AS name, s_sex AS sex, s_birthday AS birthday  FROM student WHERE s_sex = '女'</div><div>UNION</div><div>SELECT t_name AS name, t_sex AS sex, t_birthday AS birthday FROM teacher WHERE t_sex = '女';</div><div>+------+-----+---------------------+</div><div>| name | sex | birthday            |</div><div>+------+-----+---------------------+</div><div>| 王丽 | 女  | 1976-01-23 00:00:00 |</div><div>| 王芳 | 女  | 1975-02-10 00:00:00 |</div><div>| 王萍 | 女  | 1972-05-05 00:00:00 |</div><div>| 刘冰 | 女  | 1977-08-14 00:00:00 |</div><div>+------+-----+---------------------+</div></div><div><br/></div><div><br/></div><div>-- 31.查询成绩比该课程平均成绩低的同学的成绩表</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div><font color="#FF4635">注意:我的数据和视频中的数据有点不一样,所以查询结果有点区别</font></div><div>视频中score:</div><div>+------+-------+-----------+    </div><div>| s_no | c_no  | sc_degree |</div><div>+------+-------+-----------+</div><div>| 101  | 3-105 |        90 |</div><div>| 102  | 3-105 |        91 |</div><div>| 103  | 3-105 |        92 |</div><div>| 103  | 3-245 |        86 |</div><div>| 103  | 6-166 |        85 |</div><div>| 104  | 3-105 |        89 |</div><div>| 105  | 3-105 |        88 |</div><div>| 105  | 3-245 |        75 |</div><div>| 105  | 6-166 |        79 |</div><div>| 109  | 3-105 |        76 |</div><div>| 109  | 3-245 |        68 |</div><div>| <b><font color="#FF0000">109  | 6-166 |        68 |    我数据库中:    109  | 6-166 |        81</font></b></div><div>+------+-------+-----------+</div><div><br/></div><div>SELECT * FROM score AS sc1 WHERE sc1.sc_degree &lt; (SELECT  AVG(sc_degree) FROM score AS sc2 WHERE sc1.c_no = sc2.c_no);</div><div>+------+-------+-----------+  </div><div>| s_no | c_no  | sc_degree |</div><div>+------+-------+-----------+</div><div>| 105  | 3-245 |        75 |</div><div>| 105  | 6-166 |        79 |</div><div>| 109  | 3-105 |        76 |</div><div>| 109  | 3-245 |        68 |</div><div>| 109  | 6-166 |        81 |</div><div>+------+-------+-----------+</div><div><br/></div><div><font color="#5898FF">进阶,显示出学生name,课程name以及分数</font></div><div><font color="#5898FF">SELECT s.s_name ,sc1.c_no,c.c_name, sc1.sc_degree FROM score AS sc1,student AS s,course AS c WHERE sc1.sc_degree &lt; (SELECT  AVG(sc_degree) FROM score AS sc2 WHERE sc1.c_no = sc2.c_no) AND sc1.s_no = s.s_no AND sc1.c_no = c.c_no ;</font></div><div><font color="#5898FF">+--------+-------+------------+-----------+</font></div><div><font color="#5898FF">| s_name | c_no  | c_name     | sc_degree |</font></div><div><font color="#5898FF">+--------+-------+------------+-----------+</font></div><div><font color="#5898FF">| 赵铁柱 | 3-105 | 计算机导论 |        76 |</font></div><div><font color="#5898FF">| 王芳   | 3-245 | 操作系统   |        75 |</font></div><div><font color="#5898FF">| 赵铁柱 | 3-245 | 操作系统   |        68 |</font></div><div><font color="#5898FF">| 王芳   | 6-166 | 数字电路   |        79 |</font></div><div><font color="#5898FF">| 赵铁柱 | 6-166 | 数字电路   |        81 |</font></div><div><font color="#5898FF">+--------+-------+------------+-----------+</font></div></div><div><br/></div><div><br/></div><div>-- 32.查询所有<font style="color: rgb(88, 152, 255);">任课</font>教师的t_name 和 t_depart(要在分数表中可以查得到)</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>SELECT * FROM teacher WHERE t_no IN(SELECT t_no FROM course);</div><div>+------+--------+-------+---------------------+--------+------------+</div><div>| t_no | t_name | t_sex | t_birthday          | t_rof  | t_depart   |</div><div>+------+--------+-------+---------------------+--------+------------+</div><div>| 804  | 李诚   | 男    | 1958-12-02 00:00:00 | 副教授 | 计算机系   |</div><div>| 825  | 王萍   | 女    | 1972-05-05 00:00:00 | 助教   | 计算机系   |</div><div>| 831  | 刘冰   | 女    | 1977-08-14 00:00:00 | 助教   | 电子工程系 |</div><div>| 856  | 张旭   | 男    | 1969-03-12 00:00:00 | 讲师   | 电子工程系 |</div><div>+------+--------+-------+---------------------+--------+------------+</div><div><br/></div><div><span style="font-size: 9pt; font-family: Monaco;"><font color="#5898FF">注意:我个人是从score表中查出有过考试成绩的课程,再用该课程查出教师的,因为当时我个人认为只有有考试成绩才算&quot;</font><font color="#FF4635">任课</font><font color="#5898FF">&quot;,既然我写出来了,那我就放出来</font><font color="#FF4635">:(根据具体业务)</font></span></div><div><font color="#5898FF">SELECT * FROM teacher WHERE t_no IN(SELECT t_no FROM course WHERE c_no IN (SELECT c_no from score));</font></div><div><font color="#5898FF">+------+--------+-------+---------------------+--------+------------+</font></div><div><font color="#5898FF">| t_no | t_name | t_sex | t_birthday          | t_rof  | t_depart   |</font></div><div><font color="#5898FF">+------+--------+-------+---------------------+--------+------------+</font></div><div><font color="#5898FF">| 804  | 李诚   | 男    | 1958-12-02 00:00:00 | 副教授 | 计算机系   |</font></div><div><font color="#5898FF">| 825  | 王萍   | 女    | 1972-05-05 00:00:00 | 助教   | 计算机系   |</font></div><div><font color="#5898FF">| 856  | 张旭   | 男    | 1969-03-12 00:00:00 | 讲师   | 电子工程系 |</font></div><div><font color="#5898FF">+------+--------+-------+---------------------+--------+------------+</font></div></div><div><br/></div><div><br/></div><div>-- 33.查出至少有2名男生的班号</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">SELECT s_class FROM student WHERE s_sex = '男' GROUP BY s_class HAVING COUNT(s_no) &gt; 1; </span></div><div>+---------+</div><div>| s_class |</div><div>+---------+</div><div>| 95033   |</div><div>| 95031   |</div><div>+---------+</div></div><div><br/></div><div><br/></div><div>-- 34.查询student 表中 不姓&quot;王&quot;的同学的记录</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>SELECT * FROM student WHERE s_name NOT LIKE '王%'; </div><div>+------+--------+-------+---------------------+---------+</div><div>| s_no | s_name | s_sex | s_birthday          | s_class |</div><div>+------+--------+-------+---------------------+---------+</div><div>| 101  | 曾华   | 男    | 1977-09-01 00:00:00 | 95033   |</div><div>| 102  | 匡明   | 男    | 1975-10-02 00:00:00 | 95031   |</div><div>| 104  | 李军   | 男    | 1976-02-20 00:00:00 | 95033   |</div><div>| 106  | 陆军   | 男    | 1974-06-03 00:00:00 | 95031   |</div><div>| 108  | 张全蛋 | 男    | 1975-02-10 00:00:00 | 95031   |</div><div>| 109  | 赵铁柱 | 男    | 1974-06-03 00:00:00 | 95031   |</div><div>| 110  | 张飞   | 男    | 1974-06-03 00:00:00 | 95038   |</div><div>+------+--------+-------+---------------------+---------+</div></div><div><br/></div><div><br/></div><div>-- 35. 查询student 中每个学生的姓名和年龄(当前时间 - 出生年份)</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>SELECT s_name, YEAR(NOW()) - YEAR(s_birthday) AS age FROM student;</div><div>+--------+------+</div><div>| s_name | age  |</div><div>+--------+------+</div><div>| 曾华   |   42 |</div><div>| 匡明   |   44 |</div><div>| 王丽   |   43 |</div><div>| 李军   |   43 |</div><div>| 王芳   |   44 |</div><div>| 陆军   |   45 |</div><div>| 王尼玛 |   43 |</div><div>| 张全蛋 |   44 |</div><div>| 赵铁柱 |   45 |</div><div>| 张飞   |   45 |</div><div>+--------+------+</div></div><div><br/></div><div><br/></div><div>-- 36. 查询student中最大和最小的 s_birthday的值</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>SELECT MAX(s_birthday),MIN(s_birthday) FROM student;</div><div>+---------------------+---------------------+</div><div>| MAX(s_birthday)     | MIN(s_birthday)     |</div><div>+---------------------+---------------------+</div><div>| 1977-09-01 00:00:00 | 1974-06-03 00:00:00 |</div><div>+---------------------+---------------------+</div></div><div><br/></div><div><br/></div><div>-- 37.以班级号和年龄从大到小的顺序查询student表中的全部记录</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>SELECt * FROM student ORDER BY s_class DESC, s_birthday; </div><div>+------+--------+-------+---------------------+---------+</div><div>| s_no | s_name | s_sex | s_birthday          | s_class |</div><div>+------+--------+-------+---------------------+---------+</div><div>| 110  | 张飞   | 男    | 1974-06-03 00:00:00 | 95038   |</div><div>| 103  | 王丽   | 女    | 1976-01-23 00:00:00 | 95033   |</div><div>| 104  | 李军   | 男    | 1976-02-20 00:00:00 | 95033   |</div><div>| 107  | 王尼玛 | 男    | 1976-02-20 00:00:00 | 95033   |</div><div>| 101  | 曾华   | 男    | 1977-09-01 00:00:00 | 95033   |</div><div>| 106  | 陆军   | 男    | 1974-06-03 00:00:00 | 95031   |</div><div>| 109  | 赵铁柱 | 男    | 1974-06-03 00:00:00 | 95031   |</div><div>| 105  | 王芳   | 女    | 1975-02-10 00:00:00 | 95031   |</div><div>| 108  | 张全蛋 | 男    | 1975-02-10 00:00:00 | 95031   |</div><div>| 102  | 匡明   | 男    | 1975-10-02 00:00:00 | 95031   |</div><div>+------+--------+-------+---------------------+---------+</div></div><div><br/></div><div><br/></div><div>-- 38.查询&quot;男&quot;教师 及其所上的课</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>SELECT * FROM course WHERE t_no IN (SELECT t_no FROM teacher WHERE t_sex = '男');</div><div>+-------+----------+------+</div><div>| c_no  | c_name   | t_no |</div><div>+-------+----------+------+</div><div>| 3-245 | 操作系统 | 804  |</div><div>| 6-166 | 数字电路 | 856  |</div><div>+-------+----------+------+</div><div><br/></div><div>进阶:查出教师名称,教师性别, 课程名字</div><div>SELECT t.t_name,t.t_sex ,c.c_name FROM teacher t ,course c WHERE t_sex = '男' AND t.t_no = c.t_no;</div><div>+--------+-------+----------+</div><div>| t_name | t_sex | c_name   |</div><div>+--------+-------+----------+</div><div>| 李诚   | 男    | 操作系统 |</div><div>| 张旭   | 男    | 数字电路 |</div><div>+--------+-------+----------+</div></div><div><br/></div><div><br/></div><div>-- 39.查询最高分同学的s_no c_no 和 sc_degree;</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>SELECT * FROM score WHERE sc_degree = (select MAX(sc_degree) AS sc_degree FROM score);</div><div><br/></div><div>+------+-------+-----------+</div><div>| s_no | c_no  | sc_degree |</div><div>+------+-------+-----------+</div><div>| 103  | 3-105 |        92 |</div><div>+------+-------+-----------+</div></div><div><br/></div><div>-- 40. 查询和&quot;李军&quot;同性别的所有同学的s_name</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>SELECT s_name, s_sex FROM student WHERE s_SEX = (SELECT s_sex FROM student WHERE s_name = '李军');</div><div><br/></div><div>+--------+-------+</div><div>| s_name | s_sex |</div><div>+--------+-------+</div><div>| 曾华   | 男    |</div><div>| 匡明   | 男    |</div><div>| 李军   | 男    |</div><div>| 陆军   | 男    |</div><div>| 王尼玛 | 男    |</div><div>| 张全蛋 | 男    |</div><div>| 赵铁柱 | 男    |</div><div>| 张飞   | 男    |</div><div>+--------+-------+</div></div><div><br/></div><div><br/></div><div>-- 41.查询和&quot;李军&quot;同性别并且同班的所有同学的s_name</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>SELECT s_name, s_sex FROM student WHERE s_sex = (SELECT s_sex FROM student WHERE s_name = '李军') AND s_class = (SELECT s_class FROM student WHERE s_name = '李军');</div><div>+--------+-------+</div><div>| s_name | s_sex |</div><div>+--------+-------+</div><div>| 曾华   | 男    |</div><div>| 李军   | 男    |</div><div>| 王尼玛 | 男    |</div><div>+--------+-------+</div><div><br/></div><div>SELECT s_name, s_sex FROM student s1 WHERE s_sex = (SELECT s_sex FROM student s2 WHERE s_name = '李军' AND s1.s_class = s2.s_class);</div><div>+--------+-------+</div><div>| s_name | s_sex |</div><div>+--------+-------+</div><div>| 曾华   | 男    |</div><div>| 李军   | 男    |</div><div>| 王尼玛 | 男    |</div><div>+--------+-------+</div></div><div><br/></div><div><br/></div><div>-- 42. 查询所有选修'计算机导论'课程的'男'同学的成绩表</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;">SELECT * FROM score WHERE c_no = (SELECT c_no FROM course WHERE c_name = '计算机导论' ) AND s_no IN(SELECT s_no FROM student WHERE s_sex = '男');</span></div><div><span style="font-size: 9pt; color: rgb(51, 51, 51); font-family: Monaco;"><br/></span></div><div>+------+-------+-----------+</div><div>| s_no | c_no  | sc_degree |</div><div>+------+-------+-----------+</div><div>| 101  | 3-105 |        90 |</div><div>| 102  | 3-105 |        91 |</div><div>| 104  | 3-105 |        89 |</div><div>| 109  | 3-105 |        76 |</div><div>+------+-------+-----------+</div><div><br/></div><div><font color="#5898FF">进阶:显示出s_name,c_name 替代之前的 s_no and c_no</font></div><div><font color="#5898FF">SELECT s_name,c_name,sc_degree FROM score,student,course WHERE score.c_no = (SELECT c_no FROM course WHERE c_name = '计算机导论' ) AND score.s_no IN(SELECT s_no FROM student WHERE s_sex = '男') AND score.s_no = student.s_no AND score.c_no = course.c_no ;</font></div><div><font color="#5898FF">+--------+------------+-----------+</font></div><div><font color="#5898FF">| s_name | c_name     | sc_degree |</font></div><div><font color="#5898FF">+--------+------------+-----------+</font></div><div><font color="#5898FF">| 曾华   | 计算机导论 |        90 |</font></div><div><font color="#5898FF">| 匡明   | 计算机导论 |        91 |</font></div><div><font color="#5898FF">| 李军   | 计算机导论 |        89 |</font></div><div><font color="#5898FF">| 赵铁柱 | 计算机导论 |        76 |</font></div><div><font color="#5898FF">+--------+------------+-----------+</font></div></div><div><br/></div><div><br/></div><div>-- 43. 假设使用了以下命令建立了一个grade表</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>CREATE TABLE grade(</div><div>    low INT(3),</div><div>    upp INT(3),</div><div>    grade CHAR(1)</div><div>);</div><div>INSERT INTO grade VALUES(90,100,'A');</div><div>INSERT INTO grade VALUES(80,89,'B');</div><div>INSERT INTO grade VALUES(70,79,'c');</div><div>INSERT INTO grade VALUES(60,69,'D');</div><div>INSERT INTO grade VALUES(0,59,'E');</div></div><div>-- 查询所有同学的s_no , c_no 和grade列</div><div style="box-sizing: border-box; padding: 8px; font-family: Monaco, Menlo, Consolas, &quot;Courier New&quot;, monospace; font-size: 12px; color: rgb(51, 51, 51); border-radius: 4px; background-color: rgb(251, 250, 248); border: 1px solid rgba(0, 0, 0, 0.15);-en-codeblock:true;"><div>SELECT s_no, c_no , grade FROM score, grade WHERE sc_degree BETWEEN low and upp; </div><div>+------+-------+-------+</div><div>| s_no | c_no  | grade |</div><div>+------+-------+-------+</div><div>| 101  | 3-105 | A     |</div><div>| 102  | 3-105 | A     |</div><div>| 103  | 3-105 | A     |</div><div>| 103  | 3-245 | B     |</div><div>| 103  | 6-166 | B     |</div><div>| 104  | 3-105 | B     |</div><div>| 105  | 3-105 | B     |</div><div>| 105  | 3-245 | c     |</div><div>| 105  | 6-166 | c     |</div><div>| 109  | 3-105 | c     |</div><div>| 109  | 3-245 | D     |</div><div>| 109  | 6-166 | B     |</div><div>+------+-------+-------+</div><div><br/></div><div><span style="font-size: 9pt; font-family: Monaco;"><font color="#5898FF">进阶:显示学生名字和课程名称</font></span></div><div><font color="#5898FF">SELECT  s.s_no, s.s_name, c.c_name ,c.c_no , grade FROM student s, course c ,score sc, grade WHERE sc_degree BETWEEN low and upp AND sc.s_no = s.s_no AND sc.c_no = c.c_no;</font></div><div><font color="#5898FF">+------+--------+------------+-------+-------+</font></div><div><font color="#5898FF">| s_no | s_name | c_name     | c_no  | grade |</font></div><div><font color="#5898FF">+------+--------+------------+-------+-------+</font></div><div><font color="#5898FF">| 101  | 曾华   | 计算机导论 | 3-105 | A     |</font></div><div><font color="#5898FF">| 102  | 匡明   | 计算机导论 | 3-105 | A     |</font></div><div><font color="#5898FF">| 103  | 王丽   | 计算机导论 | 3-105 | A     |</font></div><div><font color="#5898FF">| 103  | 王丽   | 操作系统   | 3-245 | B     |</font></div><div><font color="#5898FF">| 103  | 王丽   | 数字电路   | 6-166 | B     |</font></div><div><font color="#5898FF">| 104  | 李军   | 计算机导论 | 3-105 | B     |</font></div><div><font color="#5898FF">| 105  | 王芳   | 计算机导论 | 3-105 | B     |</font></div><div><font color="#5898FF">| 105  | 王芳   | 操作系统   | 3-245 | c     |</font></div><div><font color="#5898FF">| 105  | 王芳   | 数字电路   | 6-166 | c     |</font></div><div><font color="#5898FF">| 109  | 赵铁柱 | 计算机导论 | 3-105 | c     |</font></div><div><font color="#5898FF">| 109  | 赵铁柱 | 操作系统   | 3-245 | D     |</font></div><div><font color="#5898FF">| 109  | 赵铁柱 | 数字电路   | 6-166 | B     |</font></div><div><font color="#5898FF">+------+--------+------------+-------+-------+</font></div></div><div><br/></div></div></span>
</div></body></html> 